﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ConsoleApp.Openxml
{
    public class OpenXml
    {
        public static void GenerateExcelWithTemplate(DataTable dt,string generateTemplate,string generatePath)
        {
            if (File.Exists(generatePath))
            {
                File.Delete(generatePath);
            }

            File.Copy(generateTemplate, generatePath);

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(generatePath, true))
            {
                WorkbookPart workbookpart = spreadsheetDocument.WorkbookPart;                
                WorksheetPart worksheetPart = workbookpart.WorksheetParts.ElementAt(0);

                SheetData data = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                IEnumerable<DataRow> results = dt.Rows.OfType<DataRow>();
                int j = 0;
                foreach (DataRow row in results)
                {
                    data.AppendChild(createContentRow(row, j + 2));
                    j = j + 1;
                }

                WorkbookProtection prot = workbookpart.Workbook.WorkbookProtection;
                if (prot == null)
                {
                    prot = new WorkbookProtection();
                    workbookpart.Workbook.WorkbookProtection = prot;
                }

                prot.WorkbookPassword = new HexBinaryValue("12334");

                workbookpart.Workbook.Save();
            }
        }

        public static void GenerateExcel(DataTable dt,string excelName)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(excelName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                SheetData data = new SheetData();
                Row header = new Row();
                header.RowIndex = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    Cell headerCell = createTextCell(dt.Columns.IndexOf(column) + 1, //Excel's column starts from 1 instead of 0
                                                     1,
                                                     column.ColumnName);

                    header.AppendChild(headerCell);
                }
                data.AppendChild(header);
                IEnumerable<DataRow> results = dt.Rows.OfType<DataRow>();
                int j = 0;
                foreach (DataRow row in results)
                {
                    data.AppendChild(createContentRow(row, j + 2));
                    j = j + 1;
                }

                worksheetPart.Worksheet.Append(data);

                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "sheet1" };

                sheets.Append(sheet);

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();
            }
        }

        private static Row createContentRow(System.Data.DataRow dataRow, int rowIndex)
        {
            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            for (int i = 0; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
                row.AppendChild(dataCell);
            }
            return row;
        }

        private static Cell createTextCell(int columnIndex,int rowIndex,object cellValue)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;

            // CellReference is the cell position, like 'A1', 'B30', where 'A' is the
            // column name and '1' is the row index.
            cell.CellReference = getColumnName(columnIndex) + rowIndex;

            InlineString inlineString = new InlineString();
            Text valueText = new Text();

            valueText.Text = cellValue.ToString();
            inlineString.AppendChild(valueText);
            cell.AppendChild(inlineString);

            return cell;
        }

        /// <summary>
        /// Convert the column index to the string name, for example column 27
        /// will return 'AA'
        /// </summary>
        /// <param name="columnIndex">Column Index</param>
        /// <returns>The string column name like 'AB'</returns>
        private static string getColumnName(int columnIndex)
        {
            int dividend = columnIndex;
            string columnName = String.Empty;
            int subIndex;

            while (dividend > 0)
            {
                subIndex = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + subIndex).ToString() + columnName;
                dividend = (dividend - subIndex) / 26;
            }

            return columnName;
        }

        /// <summary>
        /// 按照给定的Excel流组织成Datatable
        /// </summary>
        /// <param name="stream">Excel文件流</param>
        /// <param name="sheetName">须要读取的Sheet</param>
        /// <returns>组织好的DataTable</returns>
        public static DataTable ReadExcel(string sheetName, Stream stream)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
            {
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    return null;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);

                //获取Excel中共享数据
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

                //获得Excel中得数据行
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();
                DataTable dt = new DataTable("Excel");

                //因为须要将数据导入到DataTable中,所以我们假定Excel的第一行是列名,从第二行开端是行数据
                foreach (Row row in rows)
                {
                    //Excel第一行动列名
                    if (row.RowIndex == 1)
                    {
                        GetDataColumn(row, stringTable, ref dt);
                    }
                    else
                    {
                        //Excel第二行同时为DataTable的第一行数据
                        GetDataRow(row, stringTable, ref dt);
                    }
                }
                return dt;
            }
        }

        /// <summary>
        /// 构建DataTable的列
        /// </summary>
        /// <param name="row">OpenXML定义的Row对象</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">须要返回的DataTable对象</param>
        /// <returns></returns>
        private static void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                col = new DataColumn(cellVal);
                if (IsContainsColumn(dt, col.ColumnName))
                {
                    if (!columnCount.ContainsKey(col.ColumnName))
                        columnCount.Add(col.ColumnName, 0);
                    col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++);
                }
                dt.Columns.Add(col);
            }
        }

        /// <summary>
        /// 构建DataTable的每一行数据,并返回该Datatable
        /// </summary>
        /// <param name="row">OpenXML的行</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">DataTable</param>
        private static void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            // 读取算法：按行一一读取单位格,若是整行均是空数据则忽视该行(因为本人的工作内容不须要空行)-_-
            DataRow dr = dt.NewRow();
            int i = 0;
            int nullRowCount = i;
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                if (cellVal == string.Empty)
                {
                    nullRowCount++;
                }
                dr[i] = cellVal;
                i++;
            }
            if (nullRowCount != i)
            {
                dt.Rows.Add(dr);
            }
        }

        /// <summary>
        /// 获取单位格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="stringTablePart"></param>
        /// <returns></returns>
        private static string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
            }
            catch (Exception)
            {
                value = "N/A";
            }
            return value;
        }

        /// <summary>
        /// 判断网格是否存在列
        /// </summary>
        /// <param name="dt">网格</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        private static bool IsContainsColumn(DataTable dt, string columnName)
        {
            if (dt == null || columnName == null)
            {
                return false;
            }
            return dt.Columns.Contains(columnName);
        }
    }
}